package service

import (
	"encoding/json"
	"fmt"
	ClickHouse "gin/database"
	"gin/model"
	"gin/types"
	"github.com/gin-gonic/gin"
	teamones_helper "github.com/teamones-open/go-helper"
	"time"
)

type ProjectTaskProgress struct {
}

// GetEpisodeActualDeliveryData 查询剧集环节实际交付数据
func (p *ProjectTaskProgress) GetEpisodeActualDeliveryData(req types.GetProjectTaskProcessTotalReq) (res map[int64]types.EpisodePlanSealTimeCount, err error) {

	q := ClickHouse.DB.Model(&model.EpisodeStepCategorySealTime{}).
		Where("project_id = ? and seal_time between ? and ?", req.ProjectId, req.StartTime, req.EndTime).
		Select("toInt64(arrayJoin(splitByChar(',',step_category_ids))) as step_category_id,*")

	var qr []types.EpisodePlanSealTimeCountItem
	err = ClickHouse.DB.Table("(?) as es", q).
		Joins("join entity on entity.id = es.entity_id").
		Where("es.seal_time <= ?", time.Now().Unix()).
		Select("es.step_category_id step_category_id,es.entity_id as episode_id").
		Find(&qr).Error
	var resMapIndex = make(map[int64]int)
	resMapIndexCurrent := 0
	var resList []types.EpisodePlanSealTimeCount
	res = make(map[int64]types.EpisodePlanSealTimeCount)
	for _, item := range qr {
		if _, ok := resMapIndex[item.StepCategoryId]; ok {
			resList[resMapIndex[item.StepCategoryId]].DeliveryEpisodeIdList = append(resList[resMapIndex[item.StepCategoryId]].DeliveryEpisodeIdList, item.EpisodeId)
		} else {
			resList = append(resList, types.EpisodePlanSealTimeCount{
				ShouldDeliveryEpisodeIdList: []int64{},
				DeliveryEpisodeIdList:       []int64{item.EpisodeId},
				StepCategoryId:              item.StepCategoryId,
			})
			resMapIndex[item.StepCategoryId] = resMapIndexCurrent
			resMapIndexCurrent += 1
		}
	}

	for i, count := range resList {
		res[count.StepCategoryId] = resList[i]
	}

	return

}

// GetPlanEpisodeActualDeliveryData 查询计划剧集环节实际交付数据
func (p *ProjectTaskProgress) GetPlanEpisodeActualDeliveryData(projectId int64, pwhereGrouped map[int64][]int64, pWhereEndTimeCheck map[string]int64) (res map[int64]types.GetPlanEpisodeActualDeliveryDataRes, err error) {

	q := ClickHouse.DB.Model(&model.EpisodeStepCategorySealTime{}).
		Where("project_id = ?  ", projectId).
		Select("toInt64(arrayJoin(splitByChar(',',step_category_ids))) as step_category_id,*")

	var qr []types.EpisodeStepCategorySealTimeItem

	pWhere := ClickHouse.DB
	for stepCategoryId, episodeIdList := range pwhereGrouped {
		pWhere = pWhere.Or("es.step_category_id = ? and es.entity_id in ?", stepCategoryId, episodeIdList)
	}

	err = ClickHouse.DB.Table("(?) as es", q).
		Joins("join entity on entity.id = es.entity_id").
		Where(pWhere).
		Select("es.step_category_id step_category_id,es.entity_id as episode_id,es.seal_time").
		Find(&qr).Error
	if err != nil {
		return
	}
	nowUnix := time.Now().Unix()

	var resMapIndex = make(map[int64]int)
	resMapIndexCurrent := 0
	var resList []types.GetPlanEpisodeActualDeliveryDataRes
	res = make(map[int64]types.GetPlanEpisodeActualDeliveryDataRes)
	var currentStepCategoryIndex = 0
	for _, item := range qr {
		if _, ok := resMapIndex[item.StepCategoryId]; ok {
			resList[resMapIndex[item.StepCategoryId]].PlanEpisodeIdList = append(resList[resMapIndex[item.StepCategoryId]].PlanEpisodeIdList, item.EpisodeId)
		} else {
			resList = append(resList, types.GetPlanEpisodeActualDeliveryDataRes{
				PlanEpisodeIdList: []int64{item.EpisodeId},
				StepCategoryId:    item.StepCategoryId,
			})
			resMapIndex[item.StepCategoryId] = resMapIndexCurrent
			resMapIndexCurrent += 1
		}
		currentStepCategoryIndex = resMapIndex[item.StepCategoryId]
		if item.SealTime > 0 && item.SealTime <= nowUnix {
			resList[currentStepCategoryIndex].PlanDoneEpisodeIdList = append(resList[currentStepCategoryIndex].PlanDoneEpisodeIdList, item.EpisodeId)
		}
		k := fmt.Sprintf("%d-%d", item.EpisodeId, item.StepCategoryId)
		if _, ok := pWhereEndTimeCheck[k]; ok {
			if item.SealTime == 0 && pWhereEndTimeCheck[k] <= nowUnix {
				resList[currentStepCategoryIndex].PlanAbnormalEpisodeIdList = append(resList[currentStepCategoryIndex].PlanAbnormalEpisodeIdList, item.EpisodeId)
			}
		}
	}

	for i, count := range resList {
		res[count.StepCategoryId] = resList[i]
	}

	return

}

// GetStepCategoryPlanEpisodeActualDeliveryData 查询单个大环节计划剧集环节交付数据
func (p *ProjectTaskProgress) GetStepCategoryPlanEpisodeActualDeliveryData(projectId int64, pwhereGrouped map[int64][]int64, pWhereEndTimeCheck map[string]int64) (res types.GetPlanEpisodeActualDeliveryDataRes, err error) {

	if len(pwhereGrouped) == 0 {
		return
	}
	q := ClickHouse.DB.Model(&model.EpisodeStepCategorySealTime{}).
		Where("project_id = ?  ", projectId).
		Select("toInt64(arrayJoin(splitByChar(',',step_category_ids))) as step_category_id,*")

	var qr []types.EpisodeStepCategorySealTimeItem

	pWhere := ClickHouse.DB
	for stepCategoryId, episodeIdList := range pwhereGrouped {
		pWhere = pWhere.Or("es.step_category_id = ? and es.entity_id in ?", stepCategoryId, episodeIdList)
	}

	err = ClickHouse.DB.Table("(?) as es", q).
		Joins("join entity on entity.id = es.entity_id").
		Where(pWhere).
		Select("es.step_category_id step_category_id,es.entity_id as episode_id,es.seal_time").
		Find(&qr).Error
	if err != nil {
		return
	}
	nowUnix := time.Now().Unix()
	var planEpisodeMap = make(map[int64]bool)
	var doneEpisodeMap = make(map[int64]bool)
	var abnormalEpisodeMap = make(map[int64]bool)
	for _, item := range qr {
		if _, ok := planEpisodeMap[item.EpisodeId]; !ok {
			res.PlanEpisodeIdList = append(res.PlanEpisodeIdList, item.EpisodeId)
			planEpisodeMap[item.EpisodeId] = true
		}

		if item.SealTime > 0 && item.SealTime <= nowUnix {
			if _, ok := doneEpisodeMap[item.EpisodeId]; !ok {
				res.PlanDoneEpisodeIdList = append(res.PlanDoneEpisodeIdList, item.EpisodeId)
				doneEpisodeMap[item.EpisodeId] = true
			}
		}
		k := fmt.Sprintf("%d-%d", item.EpisodeId, item.StepCategoryId)
		if _, ok := pWhereEndTimeCheck[k]; ok {
			if item.SealTime == 0 && pWhereEndTimeCheck[k] <= nowUnix {
				if _, ok = abnormalEpisodeMap[item.EpisodeId]; !ok {
					res.PlanAbnormalEpisodeIdList = append(res.PlanAbnormalEpisodeIdList, item.EpisodeId)
					abnormalEpisodeMap[item.EpisodeId] = true
				}
			}
		}
	}
	return
}

// IsStandardStepCategory 是否是标准任务统计分类
func IsStandardStepCategory(showName string) bool {
	return showName == "美术" || showName == "资产" || showName == "关卡" || showName == "解算" ||
		showName == "灯光" || showName == "特效" || showName == "合成"
}

func (p *ProjectTaskProgress) GetTaskDeliveryCount(ctx *gin.Context, req types.GetProjectTaskProcessTotalReq) (res types.GetTaskDeliveryCountRes, err error) {

	rk := fmt.Sprintf("project_task_progress:%d:GetTaskDeliveryCount-%d-%d", req.ProjectId, req.StartTime, req.EndTime)
	val, _ := ClickHouse.Rdb.Get(rk).Result()
	if val != "" {
		err = json.Unmarshal([]byte(val), &res)
		if err == nil {
			return
		}
	}

	var optionService Option
	projectShowStepCategoryList, err := optionService.GetProjectShowStepCategoryListConfig(ctx)
	if err != nil {
		return
	}
	var standardTaskCountStepCategoryIdList []int64
	var shotMotionTaskCountStepCategoryIdList []int64
	projectShowStepCategoryMap := make(map[int64]string)
	for _, datum := range projectShowStepCategoryList.Data {
		projectShowStepCategoryMap[int64(datum.ID)] = datum.ShowName
		if IsStandardStepCategory(datum.ShowName) {
			standardTaskCountStepCategoryIdList = append(standardTaskCountStepCategoryIdList, int64(datum.ID))
		} else if datum.ShowName == "动画" {
			shotMotionTaskCountStepCategoryIdList = append(shotMotionTaskCountStepCategoryIdList, int64(datum.ID))
		}
	}

	var workbenchService = Workbench{}
	plist, err := workbenchService.GetPlanDoneEpisodeStepCategory(req.ProjectId, req.StartTime, req.EndTime, nil)
	if err != nil {
		return
	}
	var plistGrouped = make(map[int64][]int64)
	var standardPlanDoneGrouped = make(map[int64][]int64)
	var shotMotionPlanDoneGrouped = make(map[int64][]int64)
	for _, category := range plist {
		categoryId := category.StepCategoryId
		currentEpisodeId := category.EpisodeId
		plistGrouped[categoryId] = append(plistGrouped[categoryId], currentEpisodeId)

		if IsStandardStepCategory(projectShowStepCategoryMap[categoryId]) {
			standardPlanDoneGrouped[categoryId] = append(standardPlanDoneGrouped[categoryId], currentEpisodeId)
		} else if projectShowStepCategoryMap[categoryId] == "动画" {
			shotMotionPlanDoneGrouped[categoryId] = append(shotMotionPlanDoneGrouped[categoryId], currentEpisodeId)
		}

	}

	// 查询标准任务环节计划和交付情况
	standardTaskCount, err := p.GetStandardTaskCount(req, standardTaskCountStepCategoryIdList, standardPlanDoneGrouped)
	if err != nil {
		return
	}
	// 查询动画环节的交付情况
	shotMotionTaskCount, err := p.GetShotMotionTaskDeliveryCount(req.ProjectId, req.StartTime, req.EndTime, shotMotionTaskCountStepCategoryIdList, shotMotionPlanDoneGrouped)
	if err != nil {
		return
	}
	stepCategoryActualSealMap, err := p.GetEpisodeActualDeliveryData(req)
	if err != nil {
		return
	}
	// 剧集封板情况统计
	episodePlanSealCount, err := workbenchService.getEpisodeActualSealCount(req.ProjectId, req.StartTime, req.EndTime)
	if err != nil {
		return
	}
	checkOneTimeMap := make(map[string]bool)
	for categoryId, showName := range projectShowStepCategoryMap {
		if showName == "剧本" || showName == "分镜" || showName == "成片" {
			// 多分类环节检查是否统计过一次
			if _, ok := checkOneTimeMap[showName]; ok {
				continue
			}
			res.ShouldDeliveryTotal += int64(len(plistGrouped[categoryId]))
			checkOneTimeMap[showName] = true
			if showName == "成片" {
				// 通过剧集封板配置计算交付数据
				res.ActualDeliveryTotal += int64(len(episodePlanSealCount))

				if res.ShouldDeliveryTotal > 0 {
					for _, planEpisodeId := range plistGrouped[categoryId] {
						match := false
						for _, actualEpisodeId := range episodePlanSealCount {
							if actualEpisodeId == planEpisodeId {
								match = true
								break
							}
						}
						if match == false {
							res.ShouldDeliveryAbnormal += 1
						}
					}
				}

			} else {
				// 通过剧集环节封板配置计算交付数据
				if _, ok := stepCategoryActualSealMap[categoryId]; ok {
					res.ActualDeliveryTotal += int64(len(stepCategoryActualSealMap[categoryId].DeliveryEpisodeIdList))
				}
			}
		} else if showName == "动画" {
			// 动画统计剧集
			if !checkOneTimeMap[showName] {
				continue
			}
			res.ShouldDeliveryTotal += shotMotionTaskCount.Plan
			res.ActualDeliveryTotal += shotMotionTaskCount.ActualDone
			res.ShouldDeliveryAbnormal += shotMotionTaskCount.Abnormal
			checkOneTimeMap[showName] = true
		} else {
			// 其他走标准任务查询
			if _, ok := standardTaskCount[categoryId]; ok {
				res.ShouldDeliveryTotal += standardTaskCount[categoryId].Plan
				res.ActualDeliveryTotal += standardTaskCount[categoryId].ActualDone
				res.ShouldDeliveryAbnormal += standardTaskCount[categoryId].Abnormal
			}
		}
	}

	rs, err := json.Marshal(res)
	if err != nil {
		return
	}
	_, err = ClickHouse.Rdb.Set(rk, rs, time.Second*60).Result()
	if err != nil {
		return
	}

	return
}

// GetStandardTaskCount 查询标准任务的计划和完成情况
func (p *ProjectTaskProgress) GetStandardTaskCount(req types.GetProjectTaskProcessTotalReq, categoryIdList []int64, categoryPlan map[int64][]int64) (res map[int64]types.GetStandardTaskCountItem, err error) {
	var resList []types.GetStandardTaskCountItem
	resIndex := 0
	resMap := make(map[int64]int)
	if len(categoryPlan) > 0 {
		var planDone []types.StepCategoryCount
		pwhere := ClickHouse.DB
		for categoryId, episodeIds := range categoryPlan {
			pwhere = pwhere.Or("r_episode.link_id in ? and r_step_category.link_id = ?", episodeIds, categoryId)
		}
		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("join (select * from task_relation_tag where link_module_code = 'episode') r_episode on r_episode.task_id = task.id").
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type <> 'step_category' and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and task.project_id = ?", req.ProjectId).
			Where(pwhere).
			Group("r_step_category.link_id").
			Select("r_step_category.link_id as step_category_id,count(distinct task.id) number").
			Find(&planDone).
			Error
		if err != nil {
			return
		}
		for _, categoryCount := range planDone {
			sid := categoryCount.StepCategoryId
			if _, ok := resMap[sid]; ok {
				resList[resMap[sid]].Plan = categoryCount.Number
			} else {
				resList = append(resList, types.GetStandardTaskCountItem{
					StepCategoryId: categoryCount.StepCategoryId,
					Plan:           categoryCount.Number,
				})
				resMap[sid] = resIndex
				resIndex += 1
			}
		}

		var planDoneDone []types.StepCategoryCount

		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("join (select * from task_relation_tag where link_module_code = 'episode') r_episode on r_episode.task_id = task.id").
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type <> 'step_category' and task.project_id = ?", req.ProjectId).
			Where(pwhere).
			Where("simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0").
			Group("r_step_category.link_id").
			Select("r_step_category.link_id as step_category_id,count(distinct task.id) number").
			Find(&planDoneDone).
			Error
		if err != nil {
			return
		}
		for _, categoryCount := range planDoneDone {
			sid := categoryCount.StepCategoryId
			if _, ok := resMap[sid]; ok {
				resList[resMap[sid]].PlanDone = categoryCount.Number
			} else {
				resList = append(resList, types.GetStandardTaskCountItem{
					StepCategoryId: categoryCount.StepCategoryId,
					PlanDone:       categoryCount.Number,
				})
				resMap[sid] = resIndex
				resIndex += 1
			}
		}

		var planAbnormal []types.StepCategoryCount

		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("join (select * from task_relation_tag where link_module_code = 'episode') r_episode on r_episode.task_id = task.id").
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type <> 'step_category' and task.project_id = ?", req.ProjectId).
			Where(pwhere).
			Where("task.id in (select task_id from task_work_order where project_id = ? and status = 0) or "+
				"(task.plan_end_time > 0 and task.plan_end_time <= ? and simpleJSONExtractInt(task.json, 'task_status') not in (3,7) and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0)", req.ProjectId, time.Now().Unix()).
			Group("r_step_category.link_id").
			Select("r_step_category.link_id as step_category_id,count(distinct task.id) number").
			Find(&planAbnormal).
			Error
		if err != nil {
			return
		}

		for _, categoryCount := range planAbnormal {
			sid := categoryCount.StepCategoryId
			if _, ok := resMap[sid]; ok {
				resList[resMap[sid]].Abnormal = categoryCount.Number
			} else {
				resList = append(resList, types.GetStandardTaskCountItem{
					StepCategoryId: categoryCount.StepCategoryId,
					Abnormal:       categoryCount.Number,
				})
				resMap[sid] = resIndex
				resIndex += 1
			}
		}
	}

	var actualDone []types.StepCategoryCount
	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("join (select * from task_relation_tag where link_module_code = 'episode') r_episode on r_episode.task_id = task.id").
		Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
		Where("task.step_type <> 'step_category' and task.project_id = ? and task.end_time between ? and ?", req.ProjectId, req.StartTime, req.EndTime).
		Where("r_step_category.link_id in ? and simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0", categoryIdList).
		Group("r_step_category.link_id").
		Select("r_step_category.link_id as step_category_id,count(distinct task.id) number").
		Find(&actualDone).
		Error
	if err != nil {
		return
	}

	for _, categoryCount := range actualDone {
		sid := categoryCount.StepCategoryId
		if _, ok := resMap[sid]; ok {
			resList[resMap[sid]].ActualDone = categoryCount.Number
		} else {
			resList = append(resList, types.GetStandardTaskCountItem{
				StepCategoryId: categoryCount.StepCategoryId,
				ActualDone:     categoryCount.Number,
			})
			resMap[sid] = resIndex
			resIndex += 1
		}
	}
	res = make(map[int64]types.GetStandardTaskCountItem)
	for _, item := range resList {
		res[item.StepCategoryId] = item
	}

	return
}

// GetStepCategoryStandardTaskCount 查询单个大环节标准任务的计划和完成情况
func (p *ProjectTaskProgress) GetStepCategoryStandardTaskCount(req types.GetTaskDeliveryCountReq, categoryIdList []int64, categoryPlan map[int64][]int64) (res types.GetStandardTaskCountItem, err error) {
	if len(categoryIdList) == 0 {
		return
	}
	if len(categoryPlan) > 0 {
		pwhere := ClickHouse.DB
		for categoryId, episodeIds := range categoryPlan {
			pwhere = pwhere.Or("r_episode.link_id in ? and r_step_category.link_id = ?", episodeIds, categoryId)
		}
		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("join (select * from task_relation_tag where link_module_code = 'episode') r_episode on r_episode.task_id = task.id").
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type = 'step' and task.project_id = ?", req.ProjectId).
			Where(pwhere).
			Select("count(distinct task.id) Plan").
			Pluck("Plan", &res.Plan).
			Error
		if err != nil {
			return
		}

		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("join (select * from task_relation_tag where link_module_code = 'episode') r_episode on r_episode.task_id = task.id").
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type = 'step' and task.project_id = ?", req.ProjectId).
			Where(pwhere).
			Where("simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0").
			Select("count(distinct task.id) PlanDone").
			Pluck("PlanDone", &res.PlanDone).
			Error
		if err != nil {
			return
		}
		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("join (select * from task_relation_tag where link_module_code = 'episode') r_episode on r_episode.task_id = task.id").
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type = 'step' and task.project_id = ?", req.ProjectId).
			Where(pwhere).
			Where("task.id in (select task_id from task_work_order where project_id = ? and status = 0) or "+
				"(task.plan_end_time > 0 and task.plan_end_time <= ? and simpleJSONExtractInt(task.json, 'task_status') not in (3,7) and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0)", req.ProjectId, time.Now().Unix()).
			Select("count(distinct task.id) PlanAbnormal").
			Pluck("PlanAbnormal", &res.PlanDoneFrame).
			Error
		if err != nil {
			return
		}

	}

	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("join (select * from task_relation_tag where link_module_code = 'episode') r_episode on r_episode.task_id = task.id").
		Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
		Where("task.step_type = 'step' and task.project_id = ? and task.end_time between ? and ?", req.ProjectId, req.StartTime, req.EndTime).
		Where("r_step_category.link_id in ? and simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0", categoryIdList).
		Select("count(distinct task.id) ActualDone").
		Pluck("ActualDone", &res.ActualDone).
		Error
	if err != nil {
		return
	}
	return
}

// GetShotMotionTaskDeliveryCount 查询动画的交付统计
func (p *ProjectTaskProgress) GetShotMotionTaskDeliveryCount(projectId, startTime, endTime int64, categoryIdList []int64, categoryPlan map[int64][]int64) (res types.GetStandardTaskCountItem, err error) {

	if len(categoryIdList) == 0 {
		return
	}

	var reqStruct = map[string]interface{}{"project_id": projectId, "start_time": startTime, "end_time": endTime, "step_category_id_list": categoryIdList, "category_plan": categoryPlan}
	reqStr, _ := json.Marshal(reqStruct)
	rk := fmt.Sprintf("project_task_progress:GetShotMotionTaskDeliveryCount:%s", teamones_helper.GenerateMd5(string(reqStr)))
	val, _ := ClickHouse.Rdb.Get(rk).Result()
	if val != "" {
		err = json.Unmarshal([]byte(val), &res)
		if err == nil {
			return res, err
		}
	}
	var planDoneEpisodeIdList []int64
	if len(categoryPlan) > 0 {
		pwhere := ClickHouse.DB
		var planDoneEpisodeIdMap = make(map[int64]bool)
		for categoryId, episodeIds := range categoryPlan {
			for _, id := range episodeIds {
				if _, ok := planDoneEpisodeIdMap[id]; !ok {
					planDoneEpisodeIdList = append(planDoneEpisodeIdList, id)
					planDoneEpisodeIdMap[id] = true
				}
			}
			pwhere = pwhere.Or("task.episode_id in ? and task.step_category_id = ?", episodeIds, categoryId)
		}
		if err != nil {
			return
		}
		err = ClickHouse.DB.Model(&model.Task{}).
			Where("task.step_type = 'step' and task.project_id = ?", projectId).
			Where(pwhere).
			Where("task.id in (select task_id from task_work_order where project_id = ? and status = 0) or "+
				"(task.plan_end_time > 0 and task.plan_end_time <= ? and simpleJSONExtractInt(task.json, 'task_status') not in (3,7) and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0)", projectId, time.Now().Unix()).
			Pluck("count(distinct task.episode_id)", &res.Abnormal).
			Error
		if err != nil {
			return
		}

		entityQuery := ClickHouse.DB.Model(&model.Task{}).
			Where("task.step_type = 'step' and task.project_id = ?", projectId).
			Where(pwhere).
			Where("simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0").
			Group("task.entity_id").
			Select("task.entity_id")

		// 计划完成里 交付帧数
		err = ClickHouse.DB.Model(&model.Entity{}).
			Where("entity.id in (?)", entityQuery).
			Pluck("sum(toInt64OrZero(entity.duration))", &res.PlanDoneFrame).
			Error
		if err != nil {
			return
		}
	}
	res.Plan = int64(len(planDoneEpisodeIdList))
	// 查询出周期内完成的任务 所在剧集信息
	var currentDoneEpisodeTaskEpisodeIdList []int64
	err = ClickHouse.DB.Model(&model.Task{}).
		Where("task.step_type = 'step' and task.project_id = ?", projectId).
		Where("task.step_category_id in ? and simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and task.end_time between ? and ?", categoryIdList, startTime, endTime).
		Group("task.episode_id").
		Pluck("task.episode_id", &currentDoneEpisodeTaskEpisodeIdList).
		Error
	if err != nil {
		return
	}
	queryEpisodeDoneStatusEpisodeId := append(currentDoneEpisodeTaskEpisodeIdList, planDoneEpisodeIdList...)
	// 查询剧集是否有未完成环节任务
	if len(queryEpisodeDoneStatusEpisodeId) > 0 {
		var currentNotDoneEpisodeTaskEpisodeIdList []int64 //未完成任务剧集列表
		err = ClickHouse.DB.Model(&model.Task{}).
			Where("task.step_type = 'step' and task.project_id = ? and task.episode_id in ?", projectId, queryEpisodeDoneStatusEpisodeId).
			Where("task.step_category_id in ? and simpleJSONExtractInt(task.json, 'task_status') <> 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0 ", categoryIdList).
			Group("task.episode_id").
			Pluck("task.episode_id", &currentNotDoneEpisodeTaskEpisodeIdList).
			Error
		if err != nil {
			return
		}
		for _, checkEpisodeId := range queryEpisodeDoneStatusEpisodeId {
			notDone := false
			for _, notDoneEpisodeId := range currentNotDoneEpisodeTaskEpisodeIdList {
				if notDoneEpisodeId != checkEpisodeId {
					notDone = true
				}
			}

			if !notDone {
				// 如果一集的任务都完成了 就判断是否是本月完成的
				isCurrentMonthEpisode := false
				for _, currentMonthDoneTaskEpisodeId := range currentDoneEpisodeTaskEpisodeIdList {
					if checkEpisodeId == currentMonthDoneTaskEpisodeId {
						isCurrentMonthEpisode = true
					}
				}
				if isCurrentMonthEpisode {
					res.ActualDone += 1
				} else {
					res.PlanDone += 1
				}
			}
		}
	}

	rs, err := json.Marshal(res)
	if err != nil {
		return
	}
	_, err = ClickHouse.Rdb.Set(rk, rs, time.Second*60).Result()
	if err != nil {
		return
	}
	return

}

// GetPlanEndTimeStepCategoryCount 查询任务的完成情况
func (p *ProjectTaskProgress) GetPlanEndTimeStepCategoryCount(projectId, startTime, endTime int64, categoryIdList []int64, planWanted, actualWanted, planDoneWanted, abnormalWanted bool) (res map[int64]types.GetPlanEndTimeStepCategoryCountItem, err error) {
	if len(categoryIdList) == 0 {
		return
	}
	var resList []types.GetPlanEndTimeStepCategoryCountItem
	var resListIndex = 0
	var resStepCategoryIndex = make(map[int64]int)
	if planWanted {
		// 查询计划完成情况
		var plan []types.StepCategoryCount
		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type <> 'step_category' and task.project_id = ? and task.plan_end_time between ? and ? and r_step_category.link_id in ?", projectId, startTime, endTime, categoryIdList).
			Where("length(JSONExtractString(task.json, 'task_deprecate_data')) = 0").
			Group("r_step_category.link_id").
			Select("r_step_category.link_id as step_category_id,count(distinct task.id) number").
			Find(&plan).
			Error
		if err != nil {
			return
		}
		for _, categoryCount := range plan {
			ci := 0
			if _, ok := resStepCategoryIndex[categoryCount.StepCategoryId]; ok {
				ci = resStepCategoryIndex[categoryCount.StepCategoryId]
				resList[ci].Plan = categoryCount.Number
			} else {
				resList = append(resList, types.GetPlanEndTimeStepCategoryCountItem{
					Id:   categoryCount.StepCategoryId,
					Plan: categoryCount.Number,
				})
				resStepCategoryIndex[categoryCount.StepCategoryId] = resListIndex
				resListIndex += 1
			}
		}
	}
	if actualWanted {
		// 实际完成情况查询
		var actualDone []types.StepCategoryCount
		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type <> 'step_category' and task.project_id = ? and task.end_time between ? and ? and r_step_category.link_id in ?", projectId, startTime, endTime, categoryIdList).
			Where("r_step_category.link_id in ? and simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0", categoryIdList).
			Group("r_step_category.link_id").
			Select("r_step_category.link_id as step_category_id,count(distinct task.id) number").
			Find(&actualDone).
			Error
		if err != nil {
			return
		}
		for _, categoryCount := range actualDone {
			ci := 0
			if _, ok := resStepCategoryIndex[categoryCount.StepCategoryId]; ok {
				ci = resStepCategoryIndex[categoryCount.StepCategoryId]
				resList[ci].ActualDone = categoryCount.Number
			} else {
				resList = append(resList, types.GetPlanEndTimeStepCategoryCountItem{
					Id:         categoryCount.StepCategoryId,
					ActualDone: categoryCount.Number,
				})
				resStepCategoryIndex[categoryCount.StepCategoryId] = resListIndex
				resListIndex += 1
			}
		}
	}

	if planDoneWanted {
		// 计划完成情况查询
		var planDone []types.StepCategoryCount
		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type <> 'step_category' and task.project_id = ? and task.plan_end_time between ? and ? and r_step_category.link_id in ?", projectId, startTime, endTime, categoryIdList).
			Where("r_step_category.link_id in ? and simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0", categoryIdList).
			Group("r_step_category.link_id").
			Select("r_step_category.link_id as step_category_id,count(distinct task.id) number").
			Find(&planDone).
			Error
		if err != nil {
			return
		}
		for _, categoryCount := range planDone {
			ci := 0
			if _, ok := resStepCategoryIndex[categoryCount.StepCategoryId]; ok {
				ci = resStepCategoryIndex[categoryCount.StepCategoryId]
				resList[ci].PlanDone = categoryCount.Number
			} else {
				resList = append(resList, types.GetPlanEndTimeStepCategoryCountItem{
					Id:       categoryCount.StepCategoryId,
					PlanDone: categoryCount.Number,
				})
				resStepCategoryIndex[categoryCount.StepCategoryId] = resListIndex
				resListIndex += 1
			}
		}
	}
	if abnormalWanted {
		// 异常查询
		var planAbnormal []types.StepCategoryCount
		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type <> 'step_category' and task.project_id = ? and task.plan_end_time between ? and ? and r_step_category.link_id in ?", projectId, startTime, endTime, categoryIdList).
			Where("task.id in (select task_id from task_work_order where project_id = ? and status = 0) or "+
				"(task.plan_end_time > 0 and task.plan_end_time <= ? and simpleJSONExtractInt(task.json, 'task_status') not in (3,7) and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0)", projectId, time.Now().Unix()).
			Group("r_step_category.link_id").
			Select("r_step_category.link_id as step_category_id,count(distinct task.id) number").
			Find(&planAbnormal).
			Error
		if err != nil {
			return
		}
		for _, categoryCount := range planAbnormal {
			ci := 0
			if _, ok := resStepCategoryIndex[categoryCount.StepCategoryId]; ok {
				ci = resStepCategoryIndex[categoryCount.StepCategoryId]
				resList[ci].Abnormal = categoryCount.Number
			} else {
				resList = append(resList, types.GetPlanEndTimeStepCategoryCountItem{
					Id:       categoryCount.StepCategoryId,
					Abnormal: categoryCount.Number,
				})
				resStepCategoryIndex[categoryCount.StepCategoryId] = resListIndex
				resListIndex += 1
			}
		}
	}
	res = make(map[int64]types.GetPlanEndTimeStepCategoryCountItem)
	for i, item := range resList {
		res[item.Id] = resList[i]
	}
	return
}

// GetStepCategoryPlanEndTimeStepCategoryCount 查询单个环节任务的完成情况
func (p *ProjectTaskProgress) GetStepCategoryPlanEndTimeStepCategoryCount(projectId, startTime, endTime int64, categoryIdList []int64, planWanted, actualWanted, planDoneWanted, abnormalWanted bool) (res types.GetPlanEndTimeStepCategoryCountItem, err error) {
	if len(categoryIdList) == 0 {
		return
	}
	if planWanted {
		// 查询计划完成情况

		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type <> 'step_category' and task.project_id = ? and task.plan_end_time between ? and ? and r_step_category.link_id in ?", projectId, startTime, endTime, categoryIdList).
			Where("length(JSONExtractString(task.json, 'task_deprecate_data')) = 0").
			Select("count(distinct task.id) Plan").
			Pluck("Plan", &res.Plan).
			Error
		if err != nil {
			return
		}

	}
	if actualWanted {
		// 实际完成情况查询
		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type <> 'step_category' and task.project_id = ? and task.end_time between ? and ? and r_step_category.link_id in ?", projectId, startTime, endTime, categoryIdList).
			Where("r_step_category.link_id in ? and simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0", categoryIdList).
			Select("count(distinct task.id) ActualDone").
			Pluck("ActualDone", &res.ActualDone).
			Error
		if err != nil {
			return
		}

	}

	if planDoneWanted {
		// 计划完成情况查询
		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type <> 'step_category' and task.project_id = ? and task.plan_end_time between ? and ? and r_step_category.link_id in ?", projectId, startTime, endTime, categoryIdList).
			Where("r_step_category.link_id in ? and simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0", categoryIdList).
			Select("count(distinct task.id) PlanDone").
			Pluck("PlanDone", &res.PlanDone).
			Error
		if err != nil {
			return
		}
	}
	if abnormalWanted {
		// 异常查询
		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
			Where("task.step_type <> 'step_category' and task.project_id = ? and task.plan_end_time between ? and ? and r_step_category.link_id in ?", projectId, startTime, endTime, categoryIdList).
			Where("task.id in (select task_id from task_work_order where project_id = ? and status = 0) or "+
				"(task.plan_end_time > 0 and task.plan_end_time <= ? and simpleJSONExtractInt(task.json, 'task_status') not in (3,7) and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0)", projectId, time.Now().Unix()).
			Select("count(distinct task.id) PlanAbnormal").
			Pluck("PlanAbnormal", &res.Abnormal).
			Error
		if err != nil {
			return
		}

	}
	return
}

// GetTaskDoneCount 查询任务完成情况
func (p *ProjectTaskProgress) GetTaskDoneCount(ctx *gin.Context, req types.GetProjectTaskProcessTotalReq) (res types.GetTaskDoneCountRes, err error) {
	rk := fmt.Sprintf("project_task_progress:%d:GetTaskDoneCount-%d-%d", req.ProjectId, req.StartTime, req.EndTime)
	val, _ := ClickHouse.Rdb.Get(rk).Result()
	if val != "" {
		err = json.Unmarshal([]byte(val), &res)
		if err == nil {
			return
		}
	}
	var optionService Option
	projectShowStepCategoryList, err := optionService.GetProjectShowStepCategoryListConfig(ctx)
	if err != nil {
		return
	}
	var categoryIdList []int64
	var categoryShowNameMap = make(map[int64]string)
	for _, datum := range projectShowStepCategoryList.Data {
		categoryIdList = append(categoryIdList, int64(datum.ID))
		categoryShowNameMap[int64(datum.ID)] = datum.ShowName
	}
	stepCategoryTaskCount, err := p.GetStepCategoryPlanEndTimeStepCategoryCount(req.ProjectId, req.StartTime, req.EndTime, categoryIdList, true, true, false, true)
	if err != nil {
		return
	}
	res.ShouldDoneTotal = stepCategoryTaskCount.Plan
	res.ActualDoneTotal = stepCategoryTaskCount.ActualDone
	res.ShouldDoneAbnormal = stepCategoryTaskCount.Abnormal

	workbenchService := Workbench{}
	directorTaskCount, err := workbenchService.getDirectorTaskCount(req.ProjectId, req.StartTime, req.EndTime, false, true)

	res.ShouldDoneTotal += directorTaskCount.Total
	res.ActualDoneTotal += directorTaskCount.ActualDone
	res.ShouldDoneAbnormal += directorTaskCount.Abnormal

	rs, err := json.Marshal(res)
	if err != nil {
		return
	}
	_, err = ClickHouse.Rdb.Set(rk, rs, time.Second*60).Result()
	if err != nil {
		return
	}
	return
}

// GetWorkLoadCount 查询产能情况
func (p *ProjectTaskProgress) GetWorkLoadCount(ctx *gin.Context, req types.GetProjectTaskProcessTotalReq) (res types.GetWorkLoadCountRes, err error) {
	rk := fmt.Sprintf("project_task_progress:%d:GetWorkLoadCount-%d-%d", req.ProjectId, req.StartTime, req.EndTime)
	val, _ := ClickHouse.Rdb.Get(rk).Result()
	if val != "" {
		err = json.Unmarshal([]byte(val), &res)
		if err == nil {
			return
		}
	}

	var optionService Option
	projectShowStepCategoryList, err := optionService.GetProjectShowStepCategoryListConfig(ctx)
	if err != nil {
		return
	}
	var categoryIdList []int64
	for _, datum := range projectShowStepCategoryList.Data {
		categoryIdList = append(categoryIdList, int64(datum.ID))
	}
	// 查询进行中的任务
	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("join (select * from task_relation_tag where link_module_code = 'episode') r_episode on r_episode.task_id = task.id").
		Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
		Where("task.step_type <> 'step_category' and task.project_id = ? and task.plan_end_time between ? and ? and r_step_category.link_id in ?", req.ProjectId, req.StartTime, req.EndTime, categoryIdList).
		Where("simpleJSONExtractString(task.json, 'task_status') in (select id from status where correspond = 'in_progress')").
		Select("count(distinct task.id) inprogress_total").
		Pluck("inprogress_total", &res.InprogressTotal).
		Error
	if err != nil {
		return
	}

	// 异常查询
	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("join (select * from task_relation_tag where link_module_code = 'episode') r_episode on r_episode.task_id = task.id").
		Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
		Where("task.step_type <> 'step_category' and task.project_id = ? and task.plan_end_time between ? and ? and r_step_category.link_id in ?", req.ProjectId, req.StartTime, req.EndTime, categoryIdList).
		Where("task.id in (select task_id from task_work_order where project_id = ? and status = 0) or task.plan_end_time <= ?", req.ProjectId, time.Now().Unix()).
		Where("simpleJSONExtractInt(task.json, 'task_status') in (select id from status where correspond = 'in_progress') and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0").
		Select("count(distinct task.id) inprogress_abnormal").
		Pluck("inprogress_abnormal", &res.InprogressAbnormal).
		Error
	if err != nil {
		return
	}

	var projectMemberUserIdList []uint64

	err = ClickHouse.DB.Model(&model.ProjectMember{}).Joins("join user on user.id = project_member.user_id").
		Where("project_member.type = 'production' and project_member.project_id = ?", req.ProjectId).
		Select("distinct user.union_id union_id").
		Pluck("union_id", &projectMemberUserIdList).
		Error

	if len(projectMemberUserIdList) > 0 {

		var workHourService = WorkHour{}
		startDate := time.Unix(req.StartTime, 0).Format("2006-01-02")
		endDate := time.Unix(req.EndTime, 0).Format("2006-01-02")

		res.ProjectMemberDingWorkTimeHour, err = workHourService.QueryUserWorkHourTotal(projectMemberUserIdList, startDate+" 00:00:00", endDate+" 00:00:00", ctx)

		if err != nil {
			return res, err
		}
		res.ProjectMemberDingWorkTimeHour = res.ProjectMemberDingWorkTimeHour * 60
		err := ClickHouse.DB.Model(&model.WorkTimeRecord{}).
			Joins("join user on user.id = work_time_record.user_id").
			Where("work_time_record.record_time between ? and ? and user.union_id in ?", req.StartTime, req.EndTime, projectMemberUserIdList).
			Select("sum(work_time) AS work_time").Pluck("work_time", &res.ProjectMemberWorkTimeHour).Error
		if err != nil {
			return res, err
		}
		if res.ProjectMemberDingWorkTimeHour > 0 {
			res.ProjectMemberWorkHourPercent = float64(res.ProjectMemberWorkTimeHour) / float64(res.ProjectMemberDingWorkTimeHour)
		}
	}

	rs, err := json.Marshal(res)
	if err != nil {
		return
	}
	_, err = ClickHouse.Rdb.Set(rk, rs, time.Second*60).Result()
	if err != nil {
		return
	}
	return
}

// GetMeetingDoneCount 查询会议的完成情况
func (p *ProjectTaskProgress) GetMeetingDoneCount(projectId, startTime, endTime int64, categoryIdList []int64) (res map[int64]types.GetPlanEndTimeStepCategoryCountItem, err error) {
	if len(categoryIdList) == 0 {
		return
	}
	var resList []types.GetPlanEndTimeStepCategoryCountItem
	var resListIndex = 0
	var resStepCategoryIndex = make(map[int64]int)

	// 查询计划完成情况
	var plan []types.StepCategoryCount
	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("join plan on plan.task_id = task.id").
		Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
		Where("plan.type = 'meeting' and task.project_id = ? and task.plan_end_time between ? and ? and r_step_category.link_id in ?", projectId, startTime, endTime, categoryIdList).
		Where("length(JSONExtractString(task.json, 'task_deprecate_data')) = 0").
		Group("r_step_category.link_id").
		Select("r_step_category.link_id as step_category_id,count(distinct task.id) number").
		Find(&plan).
		Error
	if err != nil {
		return
	}
	for _, categoryCount := range plan {
		ci := 0
		if _, ok := resStepCategoryIndex[categoryCount.StepCategoryId]; ok {
			ci = resStepCategoryIndex[categoryCount.StepCategoryId]
			resList[ci].Plan = categoryCount.Number
		} else {
			resList = append(resList, types.GetPlanEndTimeStepCategoryCountItem{
				Id:   categoryCount.StepCategoryId,
				Plan: categoryCount.Number,
			})
			resStepCategoryIndex[categoryCount.StepCategoryId] = resListIndex
			resListIndex += 1
		}
	}

	// 计划完成情况查询
	var planDone []types.StepCategoryCount
	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("join plan on plan.task_id = task.id").
		Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
		Where("plan.type = 'meeting' and task.project_id = ? and task.plan_end_time between ? and ? and r_step_category.link_id in ?", projectId, startTime, endTime, categoryIdList).
		Where("r_step_category.link_id in ? and simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0", categoryIdList).
		Group("r_step_category.link_id").
		Select("r_step_category.link_id as step_category_id,count(distinct task.id) number").
		Find(&planDone).
		Error
	if err != nil {
		return
	}
	for _, categoryCount := range planDone {
		ci := 0
		if _, ok := resStepCategoryIndex[categoryCount.StepCategoryId]; ok {
			ci = resStepCategoryIndex[categoryCount.StepCategoryId]
			resList[ci].PlanDone = categoryCount.Number
		} else {
			resList = append(resList, types.GetPlanEndTimeStepCategoryCountItem{
				Id:       categoryCount.StepCategoryId,
				PlanDone: categoryCount.Number,
			})
			resStepCategoryIndex[categoryCount.StepCategoryId] = resListIndex
			resListIndex += 1
		}
	}

	res = make(map[int64]types.GetPlanEndTimeStepCategoryCountItem)
	for i, item := range resList {
		res[item.Id] = resList[i]
	}
	return
}

// GetStepCategoryMeetingDoneCount 查询环节会议的完成情况
func (p *ProjectTaskProgress) GetStepCategoryMeetingDoneCount(projectId, startTime, endTime int64, categoryIdList []int64) (res types.GetPlanEndTimeStepCategoryCountItem, err error) {
	if len(categoryIdList) == 0 {
		return
	}
	// 查询计划完成情况
	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("join plan on plan.task_id = task.id").
		Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
		Where("plan.type = 'meeting' and task.project_id = ? and task.plan_end_time between ? and ? and r_step_category.link_id in ?", projectId, startTime, endTime, categoryIdList).
		Where("length(JSONExtractString(task.json, 'task_deprecate_data')) = 0").
		Select("count(distinct task.id) Plan").
		Pluck("Plan", &res.Plan).
		Error
	if err != nil {
		return
	}

	// 计划完成情况查询
	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("join plan on plan.task_id = task.id").
		Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
		Where("plan.type = 'meeting' and task.project_id = ? and task.plan_end_time between ? and ? and r_step_category.link_id in ?", projectId, startTime, endTime, categoryIdList).
		Where("r_step_category.link_id in ? and simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0", categoryIdList).
		Select("count(distinct task.id) PlanDone").
		Pluck("PlanDone", &res.PlanDone).
		Error
	if err != nil {
		return
	}

	return
}

// GetStepCategoryProgress 查询环节任务完成情况
func (p *ProjectTaskProgress) GetStepCategoryProgress(ctx *gin.Context, req types.GetTaskDeliveryCountReq, userInfo types.UserInfoHeader) (res []types.GetStepCategoryProgressResItem, err error) {
	rk := fmt.Sprintf("project_task_progress:%d:GetStepCategoryProgress-%d-%d", req.ProjectId, req.StartTime, req.EndTime)
	val, _ := ClickHouse.Rdb.Get(rk).Result()
	if val != "" {
		err = json.Unmarshal([]byte(val), &res)
		if err == nil {
			return
		}
	}
	var optionService Option
	projectShowStepCategoryList, err := optionService.GetProjectShowStepCategoryListConfig(ctx)
	if err != nil {
		return
	}
	var standardTaskCountStepCategoryIdList []int64
	var shotMotionTaskCountStepCategoryIdList []int64
	projectShowStepCategoryMap := make(map[int64]string)
	var categoryIdList []int64
	for _, datum := range projectShowStepCategoryList.Data {
		projectShowStepCategoryMap[int64(datum.ID)] = datum.ShowName
		categoryIdList = append(categoryIdList, int64(datum.ID))
		if IsStandardStepCategory(datum.ShowName) {
			standardTaskCountStepCategoryIdList = append(standardTaskCountStepCategoryIdList, int64(datum.ID))
		} else if datum.ShowName == "动画" {
			shotMotionTaskCountStepCategoryIdList = append(shotMotionTaskCountStepCategoryIdList, int64(datum.ID))
		}
	}
	// 查询交付情况
	var workbenchService = Workbench{}
	plist, err := workbenchService.GetPlanDoneEpisodeStepCategory(req.ProjectId, req.StartTime, req.EndTime, nil)
	if err != nil {
		return
	}
	var plistGrouped = make(map[int64][]int64)
	var standardPlanDoneGrouped = make(map[int64][]int64)
	var shotMotionPlanDoneGrouped = make(map[int64][]int64)
	var episodeStepCategoryEndTimeMap = make(map[string]int64)
	for _, category := range plist {
		categoryId := category.StepCategoryId
		currentEpisodeId := category.EpisodeId
		plistGrouped[categoryId] = append(plistGrouped[categoryId], currentEpisodeId)

		if IsStandardStepCategory(projectShowStepCategoryMap[categoryId]) {
			standardPlanDoneGrouped[categoryId] = append(standardPlanDoneGrouped[categoryId], currentEpisodeId)
		} else if projectShowStepCategoryMap[categoryId] == "动画" {
			shotMotionPlanDoneGrouped[categoryId] = append(shotMotionPlanDoneGrouped[categoryId], currentEpisodeId)
		}

		k := fmt.Sprintf("%d-%d", category.EpisodeId, category.StepCategoryId)
		episodeStepCategoryEndTimeMap[k] = category.MaxEndTime // todo
	}
	// 查询标准任务环节计划和交付情况
	standardTaskCount, err := p.GetStandardTaskCount(req.GetProjectTaskProcessTotalReq, standardTaskCountStepCategoryIdList, standardPlanDoneGrouped)
	if err != nil {
		return
	}
	// 查询动画环节的交付情况
	shotMotionTaskCount, err := p.GetShotMotionTaskDeliveryCount(req.ProjectId, req.StartTime, req.EndTime, shotMotionTaskCountStepCategoryIdList, shotMotionPlanDoneGrouped)
	if err != nil {
		return
	}
	stepCategoryActualSealMap, err := p.GetPlanEpisodeActualDeliveryData(req.ProjectId, plistGrouped, episodeStepCategoryEndTimeMap)
	if err != nil {
		return
	}
	// 剧集封板情况统计
	episodePlanSealCount, err := workbenchService.getEpisodePlanSealCount(req.ProjectId, req.StartTime, req.EndTime)
	if err != nil {
		return
	}

	// 任务完成情况查询
	stepCategoryTaskCount, err := p.GetPlanEndTimeStepCategoryCount(req.ProjectId, req.StartTime, req.EndTime, categoryIdList, true, false, true, true)
	if err != nil {
		return
	}
	// 标准会议完成情况
	meetingTaskCount, err := p.GetMeetingDoneCount(req.ProjectId, req.StartTime, req.EndTime, categoryIdList)
	// 需求变更情况
	demandChangeCount, err := p.getDemandChangeCount(req.ProjectId, plistGrouped)
	if err != nil {
		return
	}
	// 工单情况
	workOrderCount, err := p.getWorkOrderCount(req.ProjectId, req.StartTime, req.EndTime, categoryIdList)
	if err != nil {
		return
	}
	// 产能情况
	workloadCount, err := p.GetStepCategoryWorkLoadCount(ctx, userInfo.TenantId, req.ProjectId, req.StartTime, req.EndTime, categoryIdList)
	if err != nil {
		return
	}
	resNameMap := make(map[string]int)
	resNameIndex := 0
	var categoryId int64 = 0
	var checkOneTimeMap = make(map[string]bool)
	for _, datum := range projectShowStepCategoryList.Data {
		currentResIndex := 0
		showName := datum.ShowName
		if _, ok := resNameMap[showName]; ok {
			currentResIndex = resNameMap[showName]
			res[currentResIndex].StepCategoryIdList = append(res[currentResIndex].StepCategoryIdList, int64(datum.ID))
			res[currentResIndex].ModuleCodeList = append(res[currentResIndex].ModuleCodeList, datum.ModuleCode)
		} else {
			res = append(res, types.GetStepCategoryProgressResItem{
				StepCategoryIdList:         []int64{int64(datum.ID)},
				StepCategoryName:           showName,
				ModuleCodeList:             []string{datum.ModuleCode},
				WorkLoadUserIdMap:          make(map[int64]bool),
				DemandChangeAddEntityIdMap: make(map[int64]int64),
				DemandChangeDelEntityIdMap: make(map[int64]int64),
				DeliverEpisodeIdMap:        make(map[int64]int64),
			})
			currentResIndex = resNameIndex
			resNameMap[showName] = resNameIndex
			resNameIndex += 1
		}
		categoryId = int64(datum.ID)

		// 处理交付数据
		if showName == "剧本" || showName == "分镜" || showName == "成片" {
			// 多分类环节检查是否统计过一次
			if checkOneTimeMap[showName] {
				continue
			}
			res[currentResIndex].DeliveryTotal = int64(len(plistGrouped[categoryId]))
			checkOneTimeMap[showName] = true
			if showName == "成片" {
				res[currentResIndex].DeliveryTotal = int64(len(episodePlanSealCount.Plan))
				res[currentResIndex].DeliveryDone = int64(len(episodePlanSealCount.Done))
				// 通过剧集封板配置计算交付数据
				if res[currentResIndex].DeliveryTotal > 0 {
					for _, planEpisodeId := range plistGrouped[categoryId] {
						match := false
						for _, actualEpisodeId := range episodePlanSealCount.Done {
							if actualEpisodeId == planEpisodeId {
								match = true
								break
							}
						}
						if match == false {
							res[currentResIndex].DeliveryAbnormal += 1
						}
					}
				}

			} else {
				// 通过剧集环节封板配置计算交付数据
				if _, ok := stepCategoryActualSealMap[categoryId]; ok {
					res[currentResIndex].DeliveryDone = int64(len(stepCategoryActualSealMap[categoryId].PlanDoneEpisodeIdList))
					res[currentResIndex].DeliveryAbnormal = int64(len(stepCategoryActualSealMap[categoryId].PlanAbnormalEpisodeIdList))
				}
			}
		} else if showName == "动画" {
			// 动画统计剧集
			if checkOneTimeMap[showName] {
				continue
			}
			res[currentResIndex].DeliveryTotal = shotMotionTaskCount.Plan
			res[currentResIndex].DeliveryDone = shotMotionTaskCount.PlanDone
			res[currentResIndex].DeliveryDoneFrame = shotMotionTaskCount.PlanDoneFrame
			res[currentResIndex].DeliveryAbnormal = shotMotionTaskCount.Abnormal
			checkOneTimeMap[showName] = true
		} else {

			// 其他走标准任务查询
			if _, ok := standardTaskCount[categoryId]; ok {
				res[currentResIndex].DeliveryTotal += standardTaskCount[categoryId].Plan
				res[currentResIndex].DeliveryDone += standardTaskCount[categoryId].PlanDone
				res[currentResIndex].DeliveryAbnormal += standardTaskCount[categoryId].Abnormal
			}

		}
		// 处理完成数据
		if _, ok := stepCategoryTaskCount[categoryId]; ok {
			res[currentResIndex].TaskTotal += stepCategoryTaskCount[categoryId].Plan
			res[currentResIndex].TaskDone += stepCategoryTaskCount[categoryId].PlanDone
			res[currentResIndex].TaskAbnormal += stepCategoryTaskCount[categoryId].Abnormal
		}
		// 处理会议情况
		if _, ok := meetingTaskCount[categoryId]; ok {
			res[currentResIndex].StandardMeetingTotal += meetingTaskCount[categoryId].Plan
			res[currentResIndex].StandardMeetingUnDone += meetingTaskCount[categoryId].Plan - meetingTaskCount[categoryId].PlanDone
		}
		// 需求变更情况
		if _, ok := demandChangeCount[categoryId]; ok {
			for _, entityId := range demandChangeCount[categoryId].AddEntityIdList {
				if _, ok = res[currentResIndex].DemandChangeAddEntityIdMap[entityId]; ok {
					continue
				}
				res[currentResIndex].DemandChangeAddEntityIdMap[entityId] = 1
				res[currentResIndex].DemandChangeAdd += 1
			}

			for _, entityId := range demandChangeCount[categoryId].DelEntityIdList {
				if _, ok = res[currentResIndex].DemandChangeDelEntityIdMap[entityId]; ok {
					continue
				}
				res[currentResIndex].DemandChangeDelEntityIdMap[entityId] = 1
				res[currentResIndex].DemandChangeDel += 1
			}
		}
		//  工单处理情况

		if _, ok := workOrderCount[categoryId]; ok {
			res[currentResIndex].WorkOrderTotal += workOrderCount[categoryId].Total
			res[currentResIndex].WorkOrderUnHandle += workOrderCount[categoryId].UnHandle
		}
		// 工作负载情况

		if _, ok := workloadCount[categoryId]; ok {
			for _, loadCount := range workloadCount[categoryId] {
				if _, ok = res[currentResIndex].WorkLoadUserIdMap[loadCount.UserId]; ok {
					continue
				}
				res[currentResIndex].WorkLoadUserIdMap[loadCount.UserId] = true
				res[currentResIndex].WorkLoadDingHourTotal += loadCount.DingWorkTimeHour
				res[currentResIndex].WorkLoadWorkHourTotal += loadCount.WorkTimeHour
				if res[currentResIndex].WorkLoadDingHourTotal > 0 {
					res[currentResIndex].WorkLoadWorkHourPercent = float64(res[currentResIndex].WorkLoadWorkHourTotal) / res[currentResIndex].WorkLoadDingHourTotal
				}
			}
		}
	}

	// 导演组的完成情况统计
	directorTaskCount, err := workbenchService.getDirectorTaskCount(req.ProjectId, req.StartTime, req.EndTime, true, false)
	if err != nil {
		return
	}
	// 导演组的标准会议情况
	directorMeetingTaskCount, err := p.getDirectorMeetingTaskCount(req.ProjectId, req.Today, req.StartTime, req.EndTime)
	if err != nil {
		return
	}
	res = append(res, types.GetStepCategoryProgressResItem{
		StepCategoryIdList:      []int64{0},
		StepCategoryName:        "导演",
		ModuleCodeList:          []string{"director"},
		TaskTotal:               directorTaskCount.Total,
		TaskDone:                directorTaskCount.Done,
		TaskAbnormal:            directorTaskCount.Abnormal,
		StandardMeetingTotal:    directorMeetingTaskCount.Plan,
		StandardMeetingUnDone:   directorMeetingTaskCount.Plan - directorMeetingTaskCount.PlanDone,
		WorkLoadWorkHourTotal:   0,
		WorkLoadDingHourTotal:   0,
		WorkLoadWorkHourPercent: 0,
	})

	rs, err := json.Marshal(res)
	if err != nil {
		return
	}
	_, err = ClickHouse.Rdb.Set(rk, rs, time.Second*60).Result()
	if err != nil {
		return
	}

	return
}

// 查询需求变更情况
func (p *ProjectTaskProgress) getDemandChangeCount(projectId int64, planGrouped map[int64][]int64) (res map[int64]types.GetDemandChangeCountResItem, err error) {

	if len(planGrouped) == 0 {
		println("don`t have delivery plan")
		return
	}
	println("have delivery plan", len(planGrouped))

	var resList []types.GetDemandChangeCountResItem
	var resStepCategoryIndex = make(map[int64]int)
	var resListIndex = 0
	var pWhere = ClickHouse.DB
	for stepCategoryId, episodeIdList := range planGrouped {
		pWhere = pWhere.Or("task.step_category_id = ? and task.episode_id in ?", stepCategoryId, episodeIdList)
	}

	type changedRowStruct struct {
		StepCategoryId int64 `json:"step_category_id"`
		EntityId       int64 `json:"entity_id"`
	}

	var added []changedRowStruct

	err = ClickHouse.DB.Model(&model.Entity{}).
		Joins("JOIN task on task.entity_id = entity.id").
		Where("task.project_id = ?", projectId).
		Where(pWhere).
		Where("entity.is_append = 'yes' and simpleJSONExtractInt(task.json, 'task_status') <> 3 and JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0").
		Select("task.step_category_id as step_category_id, entity.id as entity_id").
		Group("task.step_category_id,entity.id").
		Find(&added).
		Error
	if err != nil {
		return
	}

	for _, categoryCount := range added {
		ci := 0
		if _, ok := resStepCategoryIndex[categoryCount.StepCategoryId]; ok {
			ci = resStepCategoryIndex[categoryCount.StepCategoryId]
			resList[ci].AddEntityIdList = append(resList[ci].AddEntityIdList, categoryCount.EntityId)
		} else {
			resList = append(resList, types.GetDemandChangeCountResItem{
				Id:              categoryCount.StepCategoryId,
				AddEntityIdList: []int64{categoryCount.EntityId},
			})
			resStepCategoryIndex[categoryCount.StepCategoryId] = resListIndex
			resListIndex += 1
		}
	}

	var deleted []changedRowStruct

	err = ClickHouse.DB.Model(&model.Entity{}).
		Joins("JOIN task on task.entity_id = entity.id").
		Where(pWhere).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) > 0").
		Select("task.step_category_id as step_category_id, entity.id as entity_id").
		Group("task.step_category_id,entity.id").
		Find(&deleted).
		Error

	if err != nil {
		return
	}
	for _, categoryCount := range deleted {
		ci := 0
		if _, ok := resStepCategoryIndex[categoryCount.StepCategoryId]; ok {
			ci = resStepCategoryIndex[categoryCount.StepCategoryId]
			resList[ci].DelEntityIdList = append(resList[ci].DelEntityIdList, categoryCount.EntityId)
		} else {
			resList = append(resList, types.GetDemandChangeCountResItem{
				Id:              categoryCount.StepCategoryId,
				DelEntityIdList: []int64{categoryCount.EntityId},
			})
			resStepCategoryIndex[categoryCount.StepCategoryId] = resListIndex
			resListIndex += 1
		}
	}
	res = make(map[int64]types.GetDemandChangeCountResItem)
	for i, item := range resList {
		res[item.Id] = resList[i]
	}

	return
}

// 查询环节需求变更情况
func (p *ProjectTaskProgress) getStepCategoryDemandChangeCount(projectId int64, planGrouped map[int64][]int64) (res types.GetDemandChangeCountResItem, err error) {

	if len(planGrouped) == 0 {
		println("don`t have delivery plan")
		return
	}
	println("have delivery plan", len(planGrouped))

	var pWhere = ClickHouse.DB
	for stepCategoryId, episodeIdList := range planGrouped {
		pWhere = pWhere.Or("task.step_category_id = ? and task.episode_id in ?", stepCategoryId, episodeIdList)
	}

	err = ClickHouse.DB.Model(&model.Entity{}).
		Joins("JOIN task on task.entity_id = entity.id").
		Where("task.project_id = ?", projectId).
		Where(pWhere).
		Where("entity.is_append = 'yes' and simpleJSONExtractInt(task.json, 'task_status') <> 3 and JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0").
		Select("distinct entity.id as entity_id").
		Pluck("entity_id", &res.AddEntityIdList).
		Error
	if err != nil {
		return
	}

	err = ClickHouse.DB.Model(&model.Entity{}).
		Joins("JOIN task on task.entity_id = entity.id").
		Where(pWhere).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) > 0").
		Select("distinct entity.id as entity_id").
		Pluck("entity_id", &res.DelEntityIdList).
		Error

	return
}

// getWorkOrderCount 查询工单的处理情况
func (p *ProjectTaskProgress) getWorkOrderCount(projectId int64, startTime int64, endTime int64, categoryIdList []int64) (res map[int64]types.GetWorkOrderCountResItem, err error) {

	var workOrderCountList []types.GetWorkOrderCountResItem

	// todo 未处理的定义
	err = ClickHouse.DB.Model(&model.TaskWorkOrder{}).
		Where("project_id = ? and created between ? and ? and step_category_id in ?", projectId, startTime, endTime, categoryIdList).
		Select("step_category_id id,count(id) total,count(status in (0 ,3) or null) un_handle").
		Group("step_category_id").
		Find(&workOrderCountList).
		Error

	if err != nil {
		return
	}

	res = make(map[int64]types.GetWorkOrderCountResItem)
	for i, item := range workOrderCountList {
		res[item.Id] = workOrderCountList[i]
	}

	return
}

// 查询导演的标准会议情况
func (p *ProjectTaskProgress) getDirectorMeetingTaskCount(projectId int64, today int, startTime int64, endTime int64) (res types.GetPlanEndTimeStepCategoryCountItem, err error) {
	var directorIdList []int64
	directorQuery := ClickHouse.DB.Model(&model.ProjectMember{}).Where("role_code = 'director'")
	if projectId > 0 {
		directorQuery = directorQuery.Where("project_id = ?", projectId)
	}
	err = directorQuery.Select("distinct user_id").Find(&directorIdList).Error
	if err != nil || len(directorIdList) == 0 {
		return
	}
	// 查询计划完成情况
	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("join plan on plan.task_id = task.id join task_member on task_member.task_id = plan.task_id").
		Where("plan.type = 'meeting' and task.project_id = ? and task.plan_end_time between ? and ?", projectId, startTime, endTime).
		Where("length(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and task_member.user_id in ?", directorIdList).
		Select("count(distinct task.id) Plan").
		Pluck("Plan", &res.Plan).
		Error
	if err != nil {
		return
	}
	// 计划完成情况查询
	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("join plan on plan.task_id = task.id join task_member on task_member.task_id = plan.task_id").
		Where("plan.type = 'meeting' and task.project_id = ? and task.plan_end_time between ? and ?", projectId, startTime, endTime).
		Where("simpleJSONExtractInt(task.json, 'task_status') = 7 and length(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and task_member.user_id in ?", directorIdList).
		Select("count(distinct task.id) plan_done").
		Pluck("plan_done", &res.PlanDone).
		Error
	return
}

// GetStepCategoryWorkLoadCount 查询环节工时统计
func (p *ProjectTaskProgress) GetStepCategoryWorkLoadCount(ctx *gin.Context, tenantId, projectId, startTime, endTime int64, categoryIdList []int64) (res map[int64][]types.UserCategoryWorkLoadCount, err error) {

	var userCategoryList []types.UserCategoryItem
	err = ClickHouse.DB.Model(&model.User{}).
		Joins("join tenant_user on user.union_id = tenant_user.user_id join department on department.id = tenant_user.department_id").
		Where("user.id in (select distinct user_id from project_member where project_id = ? and type = 'production')", projectId).
		Where("tenant_user.tenant_id = ? and department.step_category_ids <> ''", tenantId).
		Select("distinct toInt64(arrayJoin(splitByChar(',', department.step_category_ids))) step_category_id, user.id user_id").
		Find(&userCategoryList).
		Error
	if err != nil || len(userCategoryList) == 0 {
		return
	}
	var distinctUserIdList []uint64
	var distinctUserIdMap = make(map[int64]bool)
	var userStepCategoryRelationMap = make(map[int64][]int64)
	for _, userCategoryItem := range userCategoryList {
		if _, ok := distinctUserIdMap[userCategoryItem.UserId]; !ok {
			distinctUserIdList = append(distinctUserIdList, uint64(userCategoryItem.UserId))
			distinctUserIdMap[userCategoryItem.UserId] = true
		}
		userStepCategoryRelationMap[userCategoryItem.StepCategoryId] = append(userStepCategoryRelationMap[userCategoryItem.StepCategoryId], userCategoryItem.UserId)
	}
	var workHourService = WorkHour{}
	startDate := time.Unix(startTime, 0).Format("2006-01-02")
	endDate := time.Unix(endTime, 0).Format("2006-01-02")
	userDingHourMap, err := workHourService.QueryUserWorkHour(distinctUserIdList, startDate+" 00:00:00", endDate+" 00:00:00", ctx)
	if err != nil {
		return res, err
	}
	var userWorkHourCount []types.UserWorkHourItem
	err = ClickHouse.DB.Model(&model.WorkTimeRecord{}).
		Where("work_time_record.record_time between ? and ? and work_time_record.user_id in ?", startTime, endTime, distinctUserIdList).
		Select("sum(work_time) AS work_hour,work_time_record.user_id AS user_id").
		Group("work_time_record.user_id").
		Find(&userWorkHourCount).Error
	if err != nil {
		return res, err
	}
	var userWorkHourCountMap = make(map[int64]int64)
	for _, item := range userWorkHourCount {
		userWorkHourCountMap[item.UserId] = item.WorkHour
	}

	var resList []types.UserCategoryWorkLoadCount
	resIndex := 0

	var dw float64 = 0
	var wr int64 = 0
	res = make(map[int64][]types.UserCategoryWorkLoadCount)
	for _, categoryId := range categoryIdList {
		if _, ok := userStepCategoryRelationMap[categoryId]; !ok {
			continue
		}
		for _, userId := range userStepCategoryRelationMap[categoryId] {
			dw = 0
			wr = 0
			if _, ok := userDingHourMap[uint64(userId)]; ok {
				dw = userDingHourMap[uint64(userId)]
			}
			if _, ok := userWorkHourCountMap[userId]; ok {
				wr = userWorkHourCountMap[userId]
			}
			resList = append(resList, types.UserCategoryWorkLoadCount{
				UserId:           userId,
				StepCategoryId:   categoryId,
				DingWorkTimeHour: dw,
				WorkTimeHour:     wr / 3600,
			})
			res[categoryId] = append(res[categoryId], resList[resIndex])
			resIndex += 1
		}
	}

	return
}

// getWorkOrderCount 查询工单的处理情况
func (p *ProjectTaskProgress) getStepCategoryWorkOrderCount(projectId int64, startTime int64, endTime int64, categoryIdList []int64) (res types.GetWorkOrderCountResItem, err error) {
	if len(categoryIdList) == 0 {
		return
	}
	// todo 未处理的定义
	err = ClickHouse.DB.Model(&model.TaskWorkOrder{}).
		Where("project_id = ? and created between ? and ? and step_category_id in ?", projectId, startTime, endTime, categoryIdList).
		Select("count(id) total,count(status in (0 ,3) or null) un_handle").
		Find(&res).
		Error

	if err != nil {
		return
	}
	return
}

// GetStepCategoryProgressRow 查询环节任务完成情况
func (p *ProjectTaskProgress) GetStepCategoryProgressRow(ctx *gin.Context, req types.GetTaskDeliveryCountReq) (res types.GetStepCategoryProgressResItem, err error) {
	reqStr, _ := json.Marshal(req)
	rk := fmt.Sprintf("project_task_progress:GetStepCategoryProgressRow:%s", teamones_helper.GenerateMd5(string(reqStr)))
	val, _ := ClickHouse.Rdb.Get(rk).Result()
	if val != "" {
		err = json.Unmarshal([]byte(val), &res)
		if err == nil {
			return
		}
	}
	var optionService Option
	projectShowStepCategoryList, err := optionService.GetProjectShowStepCategoryListConfig(ctx)
	if err != nil {
		return
	}
	var standardTaskCountStepCategoryIdList []int64
	var shotMotionTaskCountStepCategoryIdList []int64
	projectShowStepCategoryMap := make(map[int64]string)
	var categoryIdList []int64
	for _, datum := range projectShowStepCategoryList.Data {
		projectShowStepCategoryMap[int64(datum.ID)] = datum.ShowName
		if datum.ShowName != req.StepCategoryName {
			continue
		}
		res.StepCategoryIdList = append(res.StepCategoryIdList, int64(datum.ID))
		res.ModuleCodeList = append(res.ModuleCodeList, datum.ModuleCode)

		categoryIdList = append(categoryIdList, int64(datum.ID))
		if IsStandardStepCategory(datum.ShowName) {
			standardTaskCountStepCategoryIdList = append(standardTaskCountStepCategoryIdList, int64(datum.ID))
		} else if datum.ShowName == "动画" {
			shotMotionTaskCountStepCategoryIdList = append(shotMotionTaskCountStepCategoryIdList, int64(datum.ID))
		}
	}
	// 查询交付情况
	var workbenchService = Workbench{}
	plist, err := workbenchService.GetPlanDoneEpisodeStepCategory(req.ProjectId, req.StartTime, req.EndTime, categoryIdList)
	if err != nil {
		return
	}
	var plistGrouped = make(map[int64][]int64)
	var standardPlanDoneGrouped = make(map[int64][]int64)
	var shotMotionPlanDoneGrouped = make(map[int64][]int64)
	var episodeStepCategoryEndTimeMap = make(map[string]int64)
	var stepCategoryPlanDeliveryEpisodeIdList []int64
	var stepCategoryPlanDeliveryEpisodeIdMap = make(map[int64]bool)
	for _, category := range plist {
		var matched = false
		for _, inputCategoryId := range categoryIdList {
			if inputCategoryId == category.StepCategoryId {
				matched = true
				break
			}
		}
		if !matched {
			continue
		}
		categoryId := category.StepCategoryId
		currentEpisodeId := category.EpisodeId
		plistGrouped[categoryId] = append(plistGrouped[categoryId], currentEpisodeId)
		if _, ok := stepCategoryPlanDeliveryEpisodeIdMap[currentEpisodeId]; !ok {
			stepCategoryPlanDeliveryEpisodeIdList = append(stepCategoryPlanDeliveryEpisodeIdList, currentEpisodeId)
			stepCategoryPlanDeliveryEpisodeIdMap[currentEpisodeId] = true
		}
		if IsStandardStepCategory(projectShowStepCategoryMap[categoryId]) {
			standardPlanDoneGrouped[categoryId] = append(standardPlanDoneGrouped[categoryId], currentEpisodeId)
		} else if projectShowStepCategoryMap[categoryId] == "动画" {
			shotMotionPlanDoneGrouped[categoryId] = append(shotMotionPlanDoneGrouped[categoryId], currentEpisodeId)
		}
		k := fmt.Sprintf("%d-%d", category.EpisodeId, category.StepCategoryId)
		episodeStepCategoryEndTimeMap[k] = category.MaxEndTime // todo
	}

	// 标准会议完成情况
	res.StepCategoryName = req.StepCategoryName
	var workOrderCount types.GetWorkOrderCountResItem
	switch req.ColumKey {
	case types.GetTaskDeliveryCountReqColumKey.DeliveryDetail:
		// 查询剧集环节封板时间数据
		stepCategoryActualSealMap, err := p.GetStepCategoryPlanEpisodeActualDeliveryData(req.ProjectId, plistGrouped, episodeStepCategoryEndTimeMap)
		if err != nil {
			return res, err
		}
		// 剧集封板情况统计
		episodePlanSealCount, err := workbenchService.getEpisodePlanSealCount(req.ProjectId, req.StartTime, req.EndTime)
		if err != nil {
			return res, err
		}
		shotMotionTaskCount, err := p.GetShotMotionTaskDeliveryCount(req.ProjectId, req.StartTime, req.EndTime, shotMotionTaskCountStepCategoryIdList, shotMotionPlanDoneGrouped)
		// 查询标准任务环节计划和交付情况
		standardTaskCount, err := p.GetStepCategoryStandardTaskCount(req, standardTaskCountStepCategoryIdList, standardPlanDoneGrouped)
		if err != nil {
			return res, err
		}
		// 处理交付数据
		if req.StepCategoryName == "剧本" || req.StepCategoryName == "分镜" || req.StepCategoryName == "成片" {
			// 多分类环节检查是否统计过一次
			res.DeliveryTotal = int64(len(stepCategoryPlanDeliveryEpisodeIdList))
			if req.StepCategoryName == "成片" {
				res.DeliveryTotal = int64(len(episodePlanSealCount.Plan))
				res.DeliveryDone = int64(len(episodePlanSealCount.Done))
				// 通过剧集封板配置计算交付数据
				if res.DeliveryTotal > 0 {
					for _, planEpisodeId := range stepCategoryPlanDeliveryEpisodeIdList {
						match := false
						for _, actualEpisodeId := range episodePlanSealCount.Done {
							if actualEpisodeId == planEpisodeId {
								match = true
								break
							}
						}
						if match == false {
							res.DeliveryAbnormal += 1
						}
					}
				}

			} else {
				// 通过剧集环节封板配置计算交付数据
				res.DeliveryDone = int64(len(stepCategoryActualSealMap.PlanDoneEpisodeIdList))
				res.DeliveryAbnormal = int64(len(stepCategoryActualSealMap.PlanAbnormalEpisodeIdList))
			}
		} else if req.StepCategoryName == "动画" {
			// 动画统计剧集
			res.DeliveryTotal = shotMotionTaskCount.Plan
			res.DeliveryDone = shotMotionTaskCount.PlanDone
			res.DeliveryDoneFrame = shotMotionTaskCount.PlanDoneFrame
			res.DeliveryAbnormal = shotMotionTaskCount.Abnormal
		} else if req.StepCategoryName == "导演" {
			res.StepCategoryIdList = []int64{0}
			res.StepCategoryName = "导演"
			res.ModuleCodeList = []string{"director"}
		} else {
			// 其他走标准任务查询
			res.DeliveryTotal = standardTaskCount.Plan
			res.DeliveryDone = standardTaskCount.PlanDone
			res.DeliveryAbnormal = standardTaskCount.Abnormal
		}

	case types.GetTaskDeliveryCountReqColumKey.DoneDetail:

		if req.StepCategoryName == "导演" {
			// 导演组的完成情况统计
			directorTaskCount, err := workbenchService.getDirectorTaskCount(req.ProjectId, req.StartTime, req.EndTime, true, false)
			if err != nil {
				return res, err
			}
			res.StepCategoryIdList = []int64{0}
			res.StepCategoryName = "导演"
			res.ModuleCodeList = []string{"director"}
			res.TaskTotal = directorTaskCount.Total
			res.TaskDone = directorTaskCount.Done
			res.TaskAbnormal = directorTaskCount.Abnormal

		} else {
			// 任务完成情况查询
			stepCategoryTaskCount, err := p.GetStepCategoryPlanEndTimeStepCategoryCount(req.ProjectId, req.StartTime, req.EndTime, categoryIdList, true, false, true, true)
			if err != nil {
				return res, err
			}
			// 其他走标准任务查询
			res.TaskTotal = stepCategoryTaskCount.Plan
			res.TaskDone = stepCategoryTaskCount.PlanDone
			res.TaskAbnormal = stepCategoryTaskCount.Abnormal
		}
	case types.GetTaskDeliveryCountReqColumKey.StandardPlanDetail:
		if req.StepCategoryName == "导演" {
			// 导演组的标准会议情况
			directorMeetingTaskCount, err := p.getDirectorMeetingTaskCount(req.ProjectId, req.Today, req.StartTime, req.EndTime)
			if err != nil {
				return res, err
			}
			res.StepCategoryIdList = []int64{0}
			res.StepCategoryName = "导演"
			res.ModuleCodeList = []string{"director"}
			res.StandardMeetingTotal = directorMeetingTaskCount.Plan
			res.StandardMeetingUnDone = directorMeetingTaskCount.Plan - directorMeetingTaskCount.PlanDone

		} else {
			meetingTaskCount, err := p.GetStepCategoryMeetingDoneCount(req.ProjectId, req.StartTime, req.EndTime, categoryIdList)
			if err != nil {
				return res, err
			}
			// 处理会议情况
			res.StandardMeetingTotal = meetingTaskCount.Plan
			res.StandardMeetingUnDone = meetingTaskCount.Plan - meetingTaskCount.PlanDone
		}

	case types.GetTaskDeliveryCountReqColumKey.WorkOrderSituation:
		workOrderCount, err = p.getStepCategoryWorkOrderCount(req.ProjectId, req.StartTime, req.EndTime, categoryIdList)
		if err != nil {
			return
		}
		//  工单处理情况
		res.WorkOrderTotal = workOrderCount.Total
		res.WorkOrderUnHandle = workOrderCount.UnHandle
	case types.GetTaskDeliveryCountReqColumKey.DemandChangeDetail:
		// 需求变更情况
		demandChangeCount, err := p.getStepCategoryDemandChangeCount(req.ProjectId, plistGrouped)
		if err != nil {
			return res, err
		}
		// 需求变更情况
		res.DemandChangeAdd = int64(len(demandChangeCount.AddEntityIdList))
		res.DemandChangeDel = int64(len(demandChangeCount.DelEntityIdList))
	}

	rs, err := json.Marshal(res)
	if err != nil {
		return
	}
	_, err = ClickHouse.Rdb.Set(rk, rs, time.Second*60).Result()
	if err != nil {
		return
	}

	return
}
